package com.xiaozhi.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.xiaozhi.entity.Notification;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 通知数据访问层
 * 提供通知相关的数据库操作方法
 */
@Mapper
public interface NotificationMapper extends BaseMapper<Notification> {

    /**
     * 分页查询用户通知
     * @param page 分页参数
     * @param userId 用户ID
     * @param type 通知类型（可选）
     * @param status 通知状态（可选）
     * @param priority 优先级（可选）
     * @param isRead 是否已读（可选）
     * @return 分页通知列表
     */
    @Select("SELECT * FROM notifications " +
            "WHERE user_id = #{userId} AND deleted = 0 " +
            "ORDER BY created_at DESC")
    IPage<Notification> selectUserNotifications(
            Page<Notification> page,
            @Param("userId") Long userId,
            @Param("type") String type,
            @Param("status") String status,
            @Param("priority") String priority,
            @Param("isRead") Boolean isRead
    );

    /**
     * 获取用户未读通知数量
     * @param userId 用户ID
     * @return 未读通知数量
     */
    @Select("SELECT COUNT(*) FROM notifications WHERE user_id = #{userId} AND is_read = 0 AND deleted = 0")
    Integer countUnreadNotifications(@Param("userId") Long userId);

    /**
     * 获取用户未读通知数量（按类型分组）
     * @param userId 用户ID
     * @return 按类型分组的未读通知数量
     */
    @Select("SELECT type, COUNT(*) as count FROM notifications " +
            "WHERE user_id = #{userId} AND is_read = 0 AND deleted = 0 " +
            "GROUP BY type")
    List<Map<String, Object>> countUnreadNotificationsByType(@Param("userId") Long userId);

    /**
     * 获取用户最近的通知
     * @param userId 用户ID
     * @param limit 限制数量
     * @return 最近通知列表
     */
    @Select("SELECT * FROM notifications " +
            "WHERE user_id = #{userId} AND deleted = 0 " +
            "ORDER BY created_at DESC " +
            "LIMIT #{limit}")
    List<Notification> selectRecentNotifications(@Param("userId") Long userId, @Param("limit") Integer limit);

    /**
     * 获取高优先级未读通知
     * @param userId 用户ID
     * @return 高优先级未读通知列表
     */
    @Select("SELECT * FROM notifications " +
            "WHERE user_id = #{userId} AND is_read = 0 AND priority IN ('HIGH', 'URGENT') AND deleted = 0 " +
            "ORDER BY priority DESC, created_at DESC")
    List<Notification> selectHighPriorityUnreadNotifications(@Param("userId") Long userId);

    /**
     * 获取待发送的通知
     * @param limit 限制数量
     * @return 待发送通知列表
     */
    @Select("SELECT * FROM notifications " +
            "WHERE status = 'PENDING' AND deleted = 0 " +
            "AND (scheduled_at IS NULL OR scheduled_at <= NOW()) " +
            "ORDER BY priority DESC, created_at ASC " +
            "LIMIT #{limit}")
    List<Notification> selectPendingNotifications(@Param("limit") Integer limit);

    /**
     * 获取需要重试的失败通知
     * @param limit 限制数量
     * @return 需要重试的通知列表
     */
    @Select("SELECT * FROM notifications " +
            "WHERE status = 'FAILED' AND deleted = 0 " +
            "AND retry_count < max_retries " +
            "ORDER BY priority DESC, created_at ASC " +
            "LIMIT #{limit}")
    List<Notification> selectRetryableNotifications(@Param("limit") Integer limit);

    /**
     * 批量标记通知为已读
     * @param userId 用户ID
     * @param notificationIds 通知ID列表
     * @return 更新的记录数
     */
    @Update("UPDATE notifications SET is_read = 1, read_at = NOW() " +
            "WHERE user_id = #{userId} AND deleted = 0")
    Integer batchMarkAsRead(@Param("userId") Long userId, @Param("notificationIds") List<Long> notificationIds);

    /**
     * 标记所有通知为已读
     * @param userId 用户ID
     * @param type 通知类型（可选）
     * @return 更新的记录数
     */
    @Update("UPDATE notifications SET is_read = 1, read_at = NOW() " +
            "WHERE user_id = #{userId} AND is_read = 0 AND deleted = 0")
    Integer markAllAsRead(@Param("userId") Long userId, @Param("type") String type);

    /**
     * 更新通知状态
     * @param id 通知ID
     * @param status 新状态
     * @param errorMessage 错误信息（可选）
     * @return 更新的记录数
     */
    @Update("UPDATE notifications SET status = #{status}, sent_at = NOW() " +
            "WHERE id = #{id}")
    Integer updateNotificationStatus(
            @Param("id") Long id,
            @Param("status") String status,
            @Param("errorMessage") String errorMessage
    );

    /**
     * 增加重试次数
     * @param id 通知ID
     * @return 更新的记录数
     */
    @Update("UPDATE notifications SET retry_count = retry_count + 1 WHERE id = #{id}")
    Integer incrementRetryCount(@Param("id") Long id);

    /**
     * 删除过期通知
     * @param expiredTime 过期时间
     * @return 删除的记录数
     */
    @Update("UPDATE notifications SET deleted = 1 " +
            "WHERE expires_at IS NOT NULL AND expires_at < #{expiredTime}")
    Integer deleteExpiredNotifications(@Param("expiredTime") LocalDateTime expiredTime);

    /**
     * 获取通知统计信息
     * @param userId 用户ID（可选，为空时获取系统统计）
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 统计信息
     */
    @Select("SELECT " +
            "type, " +
            "status, " +
            "priority, " +
            "COUNT(*) as count, " +
            "SUM(CASE WHEN is_read = 1 THEN 1 ELSE 0 END) as read_count, " +
            "SUM(CASE WHEN is_read = 0 THEN 1 ELSE 0 END) as unread_count " +
            "FROM notifications " +
            "WHERE deleted = 0 " +
            "GROUP BY type, status, priority " +
            "ORDER BY type, priority DESC")
    List<Map<String, Object>> getNotificationStatistics(
            @Param("userId") Long userId,
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime
    );

    /**
     * 获取用户通知偏好统计
     * @param userId 用户ID
     * @return 通知偏好统计
     */
    @Select("SELECT " +
            "channel, " +
            "type, " +
            "COUNT(*) as total_count, " +
            "AVG(TIMESTAMPDIFF(MINUTE, created_at, read_at)) as avg_read_time_minutes " +
            "FROM notifications " +
            "WHERE user_id = #{userId} AND is_read = 1 AND deleted = 0 " +
            "GROUP BY channel, type " +
            "ORDER BY total_count DESC")
    List<Map<String, Object>> getUserNotificationPreferences(@Param("userId") Long userId);
}